﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;

using Model;

namespace DAL
{
    public  class EnterpriseUserDAL
    {
        public EnterpriseUserDAL()
		{}

		/// <summary>
		/// 得到最大ID
		/// </summary>
        public static int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "Admin");
        }

        public static int UpdatePassword(string userName, string passWord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_EnterpriseUsers set ");
            strSql.Append("users_pwd=").Append("'").Append(Encrypt(passWord)).Append("'").
                Append(" where users_name=").Append("'").Append(userName).Append("'");

            int nRows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return nRows;
        }

        public static string GetIncNameByUser(string userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select users_incname from ExamDB.dbo.tb_EnterpriseUsers");
            if (userName.Trim() != "")
            {
                strSql.Append(" where users_name='" + userName + "'");
            }
            DataSet ds= DbHelperSQL.Query(strSql.ToString());
            return ds.Tables[0].Rows[0][0].ToString(); ;
        }

        public static string GetEnterpriseIdByUser(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select users_id from ExamDB.dbo.tb_EnterpriseUsers");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where users_name='" + strWhere + "'");
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            string enterpriseId = ds.Tables[0].Rows[0][0].ToString();
            return enterpriseId ;
        }

        /// <summary>
        /// 判断登录
        /// </summary>
        public static bool CheckLogin(EnterpriseUser enterpriseUser)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ExamDB.dbo.tb_EnterpriseUsers");
            strSql.Append(" where users_name=@Username");
            strSql.Append(" and users_pwd=@Password");
            SqlParameter[] parameters ={
                   new SqlParameter("@Username",SqlDbType.VarChar,255),
                   new SqlParameter("@Password",SqlDbType.VarChar,255)};
            parameters[0].Value = enterpriseUser.Username;
            parameters[1].Value = Encrypt(enterpriseUser.Password);

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

		/// <summary>
		/// 是否存在该记录
		/// </summary>
        public static bool Exists(int ID)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select count(1) from ExamDB.dbo.tb_EnterpriseUsers");
            strSql.Append(" where users_id=@ID ");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
			parameters[0].Value = ID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public static bool Exists(string  userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ExamDB.dbo.tb_EnterpriseUsers");
            strSql.Append(" where users_name=@users_name ");
            SqlParameter[] parameters = {
					new SqlParameter("@users_name", SqlDbType.NVarChar,255)};
            parameters[0].Value = userName;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


		/// <summary>
		/// 增加一条数据
		/// </summary>
        public static int Add(EnterpriseUser model)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("insert into ExamDB.dbo.tb_EnterpriseUsers(");
            strSql.Append(@"users_name,
                            users_pwd,
                            users_incname,
                            users_contact,
                            users_phone,
                            users_address,
                            users_firstlogontime,
                            users_availablemoney,
                            users_totalconsumemoney)");

			strSql.Append(" values (");

            strSql.Append(@"@users_name,
                            @users_pwd,
                            @users_incname,
                            @users_contact,
                            @users_phone,
                            @users_address,
                            @users_firstlogontime,
                            @users_availablemoney,
                            @users_totalconsumemoney)");

			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@users_name", SqlDbType.NVarChar,255),
					new SqlParameter("@users_pwd", SqlDbType.NChar,255),
					new SqlParameter("@users_incname", SqlDbType.NVarChar,255),
					new SqlParameter("@users_contact", SqlDbType.NVarChar,255),
                    new SqlParameter("@users_phone",SqlDbType.NVarChar,255),
					new SqlParameter("@users_address", SqlDbType.NVarChar,255),
					new SqlParameter("@users_firstlogontime", SqlDbType.DateTime),
                    new SqlParameter("@users_availablemoney", SqlDbType.Float),
                    new SqlParameter("@users_totalconsumemoney", SqlDbType.Float)
                                        };
			parameters[0].Value = model.Username;
            parameters[1].Value = Encrypt(model.Password);
            parameters[2].Value = model.Users_incname;
            parameters[3].Value = model.Users_contact; 
			parameters[4].Value = model.Users_phone;
			parameters[5].Value = model.Users_address;
            parameters[6].Value = model.Users_firstlogon;
            parameters[7].Value = model.Users_availablemoney;
            parameters[8].Value = model.Users_totalconsumemoney;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return -1;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
        public static int Update(EnterpriseUser model)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_EnterpriseUsers set ");
            strSql.Append("users_name=@users_name,");
            strSql.Append("users_pwd=@users_pwd,");
            strSql.Append("users_incname=@users_incname,");
            strSql.Append("users_contact=@users_contact,");
            strSql.Append("users_phone=@users_phone,");
            strSql.Append("users_address=@users_address");
            strSql.Append(" where users_id=@users_id ");
			SqlParameter[] parameters = {
					new SqlParameter("@users_id", SqlDbType.Int,4),
					new SqlParameter("@users_name", SqlDbType.NVarChar,255),
					new SqlParameter("@users_pwd", SqlDbType.NChar,255),
					new SqlParameter("@users_incname", SqlDbType.NVarChar,255),
					new SqlParameter("@users_contact", SqlDbType.NVarChar,255),
                    new SqlParameter("@users_phone",SqlDbType.NVarChar,255),
					new SqlParameter("@users_address", SqlDbType.NVarChar,255)};
            parameters[0].Value = model.Id;
            parameters[1].Value = model.Username;
            parameters[2].Value = Encrypt(model.Password);
            parameters[3].Value = model.Users_incname;
            parameters[4].Value = model.Users_contact;
            parameters[5].Value = model.Users_phone;
            parameters[6].Value = model.Users_address;

			return  DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
		}

        public static int UpdateFirstTimeLogon(string users_id, DateTime updateTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT users_firstlogontime FROM dbo.tb_EnterpriseUsers WHERE users_id='");
            strSql.Append(users_id + "'");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows[0][0].ToString() != String.Empty)
            {
                return 1;
            }
            else
            {
                strSql = null;
                strSql = new StringBuilder();
                strSql.Append("update ExamDB.dbo.tb_EnterpriseUsers set users_firstlogontime=' ");
                strSql.Append(updateTime.ToString());
                strSql.Append("' where users_id='");
                strSql.Append(users_id + "'");
                if (DbHelperSQL.Query(strSql.ToString()) != null)
                {
                    return 1;
                }
                else
                {
                    return -1;
                }
            }
           
        }

        /// <summary>
        /// 更新联系信息
        /// </summary>
        public static int UpdateContact(EnterpriseUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_EnterpriseUsers set ");
            strSql.Append("users_incname=@users_incname,");
            strSql.Append("users_contact=@users_contact,");
            strSql.Append("users_phone=@users_phone,");
            strSql.Append("users_address=@users_address,");
            strSql.Append("users_type=@users_type,");
            strSql.Append("users_scale=@users_scale");
            strSql.Append(" where users_name=@users_name ");
            SqlParameter[] parameters = {
					new SqlParameter("@users_incname", SqlDbType.NVarChar,255),
					new SqlParameter("@users_contact", SqlDbType.NVarChar,255),
					new SqlParameter("@users_phone", SqlDbType.NChar,255),
					new SqlParameter("@users_address", SqlDbType.NVarChar,255),
					new SqlParameter("@users_type", SqlDbType.NVarChar,10),
                    new SqlParameter("@users_scale",SqlDbType.NVarChar,10),
					new SqlParameter("@users_name", SqlDbType.NVarChar,255)};
            parameters[0].Value = model.Users_incname;
            parameters[1].Value = model.Users_contact;
            parameters[2].Value = model.Users_phone;
            parameters[3].Value = model.Users_address;
            parameters[4].Value = model.Users_type;
            parameters[5].Value = model.Users_scale;
            parameters[6].Value = model.Username;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
        public static void Delete(int ID)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("delete ExamDB.dbo.tb_EnterpriseUsers ");
            strSql.Append(" where users_id=@ID ");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
			parameters[0].Value = ID;

			DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
        public static EnterpriseUser GetModel(int ID)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 * from ExamDB.dbo.tb_EnterpriseUsers");
            strSql.Append(" where users_id=@ID ");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
			parameters[0].Value = ID;

			EnterpriseUser  model=new  EnterpriseUser();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
                if (ds.Tables[0].Rows[0]["users_id"].ToString() != "")
				{
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["users_id"].ToString());
				}

                model.Username = ds.Tables[0].Rows[0]["users_name"].ToString();
                model.Password = ds.Tables[0].Rows[0]["users_pwd"].ToString();
                model.Users_incname=ds.Tables[0].Rows[0]["users_incname"].ToString();
                model.Users_contact = ds.Tables[0].Rows[0]["users_contact"].ToString();
                model.Users_phone = ds.Tables[0].Rows[0]["users_phone"].ToString();
                model.Users_address = ds.Tables[0].Rows[0]["users_address"].ToString(); 
                model.Users_availablemoney = float.Parse(ds.Tables[0].Rows[0]["users_availablemoney"].ToString());
                if (ds.Tables[0].Rows[0]["users_firstlogontime"].ToString() != "")
                {
                    model.Users_firstlogon = DateTime.Parse(ds.Tables[0].Rows[0]["users_firstlogontime"].ToString());
                }
             
                model.Users_totalconsumemoney = float.Parse(ds.Tables[0].Rows[0]["users_totalconsumemoney"].ToString());
                model.Users_scale = ds.Tables[0].Rows[0]["users_scale"].ToString();
                model.Users_type = ds.Tables[0].Rows[0]["users_type"].ToString(); 
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
        public static DataSet GetListAll()
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append(@"select users_id,
            users_name,
            users_incname,
            users_contact,
            users_phone, 
            (SELECT SUM(payment_money) FROM dbo.tb_Activity_Payment 
            WHERE activity_id IN(SELECT activity_id FROM dbo.tb_Activity WHERE dbo.tb_Activity.activity_enterpriseuid=dbo.tb_EnterpriseUsers.users_id )) as users_totalconsumemoney ,
            users_firstlogontime 
            FROM dbo.tb_EnterpriseUsers
                           ");
        
			return DbHelperSQL.Query(strSql.ToString());
		}

        public static bool CheckLogin(AdminUser adminUser)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select count(1) from ExamDB.dbo.tb_AdminUsers");

            strSql.Append(" where users_name=@userName and users_password=@userPassword");

            SqlParameter[] parameters = {new SqlParameter("@userName", SqlDbType.VarChar,255),
                                            new SqlParameter("@userPassword",SqlDbType.VarChar,255)};

            parameters[0].Value = adminUser.UserNm;
            parameters[1].Value = Encrypt(adminUser.UserPw);
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 用户加密函数，对于用户密码进行MD5加密
        /// </summary>
        public static String Encrypt(string password)
        {
            Byte[] clearBytes = new UnicodeEncoding().GetBytes(password);
            Byte[] hashedBytes = ((HashAlgorithm)CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);

            return BitConverter.ToString(hashedBytes);
        }
    }
}
